# coding=utf-8
import sqlite3
import math
from Utils.FileUtils import *
import pandas as pd
import os
from DataBase.SQLiteTable import MySQLiteDatabase
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker


class SQLiteConnect:

    connect = None

    def __init__(self, path='', name=''):
        path = os.path.join(path, name)
        if path is not None:
            self.connect = sqlite3.connect(path)

    def __del__(self):
        if self.connect is not None:
            self.connect.close()

    def get_connect(self):
        return self.connect

    @staticmethod
    def get_sqlite_db_uri(path=''):
        db_uri = "sqlite:///{path}".format(path=path)
        return db_uri


class SQLiteObject:
    dataBaseFolderPath = ''

    def __init__(self):
        pass

    @staticmethod
    def init_table(con=None, table='', frame=None, keys=None, if_exists='append', index=None,
                    index_label=None, schema=None, dtype=None):
        data_base = MySQLiteDatabase(con, False)
        data_base.init_table(frame, table, if_exists=if_exists,schema=schema, dtype=dtype,keys=keys, index=index, index_label=index_label)

    @staticmethod
    def is_database_include(con=None, table='', key_column='', key=''):
        if (con is None) or (len(table) == 0) or (len(key_column) == 0) or (len(key) == 0):
            return True

        sql = 'SELECT * FROM \'' + table + '\' Where ' + key_column + ' == \'' + key + '\''
        try:
            df = pd.io.sql.read_sql(sql, con=con)
            if len(df) > 0:
                return True

        except Exception as e:
            print(e)
            raise e
            return True
        finally:
            pass
        return False

    @staticmethod
    def insert_ignore(con=None, table='', columns=[], df=None):
        sql = 'INSERT OR IGNORE \'' + table + '\' ( '
        index = 0
        value_tuple = ()
        PlaceHolder = " ("
        try:
            while index < len(df.values):
                item = df.loc[index]

                for column in columns:
                    key_item = column
                    value_item = item[index]
                    if not isinstance(value_item, str) and math.isnan(value_item):
                        value_item = "0"

                    if index == 0:
                        sql += " \"" + key_item + '\" = ? '
                        PlaceHolder += " %s"
                    else:
                        sql += ", \"" + key_item + '\" = ? '
                        PlaceHolder += " ,%s"

                    value_tuple += tuple((value_item,))
                    index += 1
                sql += ' VALUES ' + PlaceHolder + ');'
                con.execute(sql, value_tuple)
        except Exception as e:
            print(sql)
            print(e)
            return False
        finally:
            con.commit()
            pass
        return True

    @staticmethod
    def save_data_bat(con=None, table='', key_column='', df=None):
        key_conflict = False
        try:
            pd.io.sql.to_sql(df, table, con=con, if_exists='append', index=False)
        except sqlite3.IntegrityError as e:
            str_repeat_record = 'UNIQUE constraint failed: ' + table
            if (e is not None) and (e.args is not None) and e.args[0].startswith(str_repeat_record,
                                                                                 0,
                                                                                 len(str_repeat_record)):
                key_conflict = True
            else:
                raise e
        finally:
            if key_conflict:
                # 数据库key冲突，因为更新数据会有些旧数据重复，因此需要把新数据挑出来
                data_new = pd.DataFrame()
                index = 1
                while index < len(df.values):
                    item = df.loc[index]
                    str_key = str(item[key_column])
                    check_data_exist = SQLiteObject.is_database_include(con=con, table=table, key_column=key_column, key=str_key)
                    if not check_data_exist:
                        # 还要检查当前队列里是不是也有这个记录
                        if data_new.empty:
                            data_new = data_new.append(item, ignore_index=True)
                        else:
                            a = data_new[data_new[key_column] == str_key]
                            if a.empty:
                                data_new = data_new.append( item, ignore_index=True)
                    index += 1

                if len(data_new):
                    try:
                        pd.io.sql.to_sql(data_new, table, con=con, if_exists='append', index=False)
                    except sqlite3.IntegrityError as e:
                        str_repeat_record = 'UNIQUE constraint failed: ' + table
                        if (e is not None) and (e.args is not None) and e.args[0].startswith(str_repeat_record,
                                                                                             0,
                                                                                             len(str_repeat_record)):
                            pass
                        else:
                            raise e
                    finally:
                        pass

    @staticmethod
    def save_data(path='', name='', table='', df=None, index_label=None, if_exists='append'):
        path = os.path.join(path, name)
        con = sqlite3.connect(path)
        pd.io.sql.to_sql(df, table, con=con, if_exists=if_exists, index=False, index_label=index_label)
        con.close()

    @staticmethod
    def get_data_condition(con=None, table='',where=''):
        df = None
        sql = 'SELECT * FROM \'' + table + '\' '
        if not where.strip() == '':
            sql += where

        df = pd.io.sql.read_sql(sql, con=con)
        return df

    @staticmethod
    def get_data_ex(con=None, table='', sql='', from_date='' , end_date='', where='', orderby=''):
        df = None
        if not sql.strip() == '':
            df = pd.io.sql.read_sql(sql, con=con)
        elif not table.strip() == '':
            if not from_date.strip() == '':
                sql = 'SELECT * FROM \'' + table + '\' Where time_key >= \'' + from_date + '\' '
                if not end_date.strip() == '':
                    sql += ' and time_key <= \'' + end_date + '\' '
            elif not end_date.strip() == '':
                sql = 'SELECT * FROM \'' + table + '\' Where time_key <= \'' + end_date + '\' '
            else:
                sql = 'SELECT * FROM \'' + table + "\' "

            if not where.strip() == '':
                sql += where + " "

            if not orderby.strip() == '':
                sql += orderby + " "

            df = pd.io.sql.read_sql(sql, con=con)
        else:
            pass
        return df

    @staticmethod
    def get_tablesname(con=None):
        result = None
        if (con is None):
            return result

        sql = 'SELECT name FROM sqlite_master WHERE type=\'table\' order by name ;'
        try:
            result = pd.io.sql.read_sql(sql, con=con)
        except Exception as e:
            print(e)
        finally:
            pass

        return result


    @staticmethod
    def drop_table(con=None, table=''):
        result = False
        if (con is None) or (len(table) == 0):
            return result

        sql = 'drop table \'' + table + '\''
        try:
            pd.io.sql.execute(sql, con=con)
            result = True
        except Exception as e:
            print(e)
        finally:
            pass

        return result

    @staticmethod
    def update_value(con=None,table='', main_key='', main_key_value=None, value_key=[], value=[], commit=False):
        if (con is None) or (len(table) == 0) or (len(main_key) == 0) or (len(value_key) == 0) or (len(value_key) != len(value)):
            return False

        sql = 'update \'' + table + '\' set '
        index = 0
        value_tuple = ()
        while index < len(value_key):
            key_item = value_key[index]
            value_item = value[index]
            if not isinstance(value_item, str) and math.isnan(value_item):
                value_item = "0"

            if index == 0:
                sql += " \"" + key_item + '\" = ? '
            else:
                sql += ", \"" + key_item + '\" = ? '
            value_tuple += tuple((value_item,))
            index += 1
        sql += ' Where \"' + main_key + '\" = ? ;'
        value_tuple += tuple((main_key_value,))
        try:
            con.execute(sql, value_tuple)
            if commit:
                con.commit()

        except Exception as e:
            print(sql)
            print(e)
            raise e
            return False
        finally:
            pass
        return True

    @staticmethod
    def update_multi_value(con=None,table='', data=None, main_key='', update_keys=[]):
        if (con is None) or (len(table) == 0) or (len(main_key) == 0) or (data is None) or (len(update_keys) == 0):
            return False

        index = 0
        while index < data.index.size:
            item = data.loc[index]
            sql = 'update \'' + table + '\' set '
            update_index = 0
            while update_index < len(update_keys):
                if update_index == 0:
                    sql += ' '+ update_keys[update_index] + ' = ' + str(item[update_keys[update_index]])
                else:
                    sql += ', ' + update_keys[update_index] + ' = ' + str(item[update_keys[update_index]])
                update_index += 1

            sql += ' Where ' + main_key + ' == \'' + item[main_key] + '\' ;'
            try:
                print(sql)
                con.execute(sql)
            except Exception as e:
                print(e)
                raise e
                return False
            finally:
                pass
            index += 1
        con.commit()
        return True

    @staticmethod
    def execute_sql(con=None, sql=''):
        if (con is None) or (len(sql) == 0):
            return False

        try:
            con.execute(sql)
        except Exception as e:
            print(e)
            return False
        finally:
            con.commit()
        return True

    @staticmethod
    def get_data(path='', name='', table='', sql=''):
        path = os.path.join(path, name)
        con = sqlite3.connect(path)
        df = None
        if not sql.strip() == '':
            df = pd.io.sql.read_sql(sql, con=con)
        elif not table.strip() == '':
            sql = 'SELECT * FROM ' + table
            df = pd.io.sql.read_sql(sql, con=con)
        else:
            pass
        con.close()
        return df

    @staticmethod
    def remove_database_file(path='', name=''):
        FileUtils.remove_file(path, name)

    @staticmethod
    def is_table_exist(con=None, table=''):
        if (con is None) or (len(table) == 0):
            return False

        sql = 'select count(*)  from sqlite_master where type=\'table\' and name = \'' + table + '\''
        result = None
        try:
            result = pd.io.sql.read_sql(sql, con=con)
        except Exception as e:
            print(e)
            raise e
            return False
        finally:
            pass

        if result.loc[0]['count(*)'] >= 1:
            return True

        return False

    @staticmethod
    def get_db_info(con=None, type='table'):
        result = None
        if (con is None):
            return result

        sql = 'select * from sqlite_master where type = \'' + type + '\''
        if (len(type) == 0):
            sql = 'select * from sqlite_master'
        try:
            result = pd.io.sql.read_sql(sql, con=con)
        except Exception as e:
            print(e)
            raise e
        finally:
            pass

        return result

    @staticmethod
    def get_tables(con=None, limit_up=250):
        if con is None:
            return None

        sql = 'SELECT name FROM sqlite_master WHERE type=\'table\' ORDER BY name limit {};'.format(limit_up)
        result = None
        try:
            result = pd.io.sql.read_sql(sql, con=con)
        except Exception as e:
            print(e)
            raise e
            return result
        finally:
            pass

        return result

